{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1e618c1e-a6c4-41c7-a771-98a0fc2f68c8",
   "metadata": {},
   "source": [
    "This notebook shows that Mosaic works with a serverless DuckDB using [MotherDuck](https://motherduck.com). We are using the [Gaia dataset](http://cdn.gea.esac.esa.int/Gaia/gdr3/gaia_source/) from a [share](https://motherduck.com/docs/key-tasks/querying-a-shared-motherduck-database)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09350f79-b7fe-4598-b2b9-a95bfdb6d2e2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import duckdb\n",
    "import yaml\n",
    "\n",
    "from mosaic_widget import MosaicWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56c87785-df46-4a0f-a973-957cd9264561",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con = duckdb.connect()\n",
    "\n",
    "share = \"md:_share/gaia-import/c551f2da-3258-4387-ac43-f4be601850ac\"\n",
    "con.execute(f\"ATTACH '{share}' AS share\")\n",
    "\n",
    "con.execute(\"ATTACH ':memory:' AS local\")\n",
    "# con.execute(\"ATTACH 'indexes.db' AS local\") # use a local database for the indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a7d6766-7bda-472b-b5a5-ee098fcb64c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"../../dev/yaml/gaia.yaml\") as f:\n",
    "    spec = yaml.safe_load(f)\n",
    "\n",
    "# use existing table\n",
    "del spec[\"data\"]\n",
    "\n",
    "# we use a sample (code to create it below)\n",
    "table = \"share.gaia_sample_5_percent_projected\"\n",
    "\n",
    "spec[\"hconcat\"][0][\"vconcat\"][0][\"plot\"][0][\"data\"][\"from\"] = table\n",
    "spec[\"hconcat\"][0][\"vconcat\"][1][\"hconcat\"][0][\"plot\"][0][\"data\"][\"from\"] = table\n",
    "spec[\"hconcat\"][0][\"vconcat\"][1][\"hconcat\"][1][\"plot\"][0][\"data\"][\"from\"] = table\n",
    "spec[\"hconcat\"][2][\"plot\"][0][\"data\"][\"from\"] = table\n",
    "spec;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0245f0f3-ef07-4540-876c-4c788ee5cde0",
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(\"USE local\")\n",
    "MosaicWidget(con=con, spec=spec, temp_indexes=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7bf5725-693f-4e0b-b5e4-06ea4f82c1bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show the indexes\n",
    "con.query(\"SHOW TABLES\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9cf8b8d-7016-43d0-8017-902f406e281e",
   "metadata": {},
   "source": [
    "## Set up queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9688132b-f6e5-4356-bafa-b234da8e0e23",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Install MotherDuck\n",
    "\n",
    "c = duckdb.connect()\n",
    "c.query(\"FORCE INSTALL motherduck\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4c77342-c19c-4e83-9068-2e4b80191612",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show all tables in the share\n",
    "\n",
    "c = duckdb.connect()\n",
    "c.execute(f\"ATTACH '{share}' AS share\")\n",
    "c.execute(\"USE share\")\n",
    "c.query(\"SHOW TABLES\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59d8673b-8905-40b0-aa88-9b6167f2a6e1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a table with the u and v columns with natural earth projection.\n",
    "# This will not work in the share and assumes a specific setup.\n",
    "\n",
    "c = duckdb.connect(\"md:gaia-import\")\n",
    "\n",
    "c.execute(\n",
    "    \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS gaia_sample_5_percent AS\n",
    "    SELECT l, b, ra, dec, phot_g_mean_mag, parallax, bp_rp\n",
    "    FROM gaia\n",
    "    USING SAMPLE 5 PERCENT (bernoulli)\n",
    "\"\"\"\n",
    ")\n",
    "\n",
    "c.execute(\n",
    "    \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS gaia_sample_5_percent_projected AS\n",
    "WITH tmp AS (\n",
    "  SELECT\n",
    "    radians((-l + 540) % 360 - 180) AS lambda,\n",
    "    radians(b) AS phi,\n",
    "    asin(sqrt(3)/2 * sin(phi)) AS t,\n",
    "    t^2 AS t2,\n",
    "    t2^3 AS t6,\n",
    "    *\n",
    "  FROM gaia_sample_5_percent\n",
    ")\n",
    "SELECT\n",
    "  (1.340264 * lambda * cos(t)) / (sqrt(3)/2 * (1.340264 + (-0.081106 * 3 * t2) + (t6 * (0.000893 * 7 + 0.003796 * 9 * t2)))) AS u,\n",
    "  t * (1.340264 + (-0.081106 * t2) + (t6 * (0.000893 + 0.003796 * t2))) AS v,\n",
    "  * EXCLUDE('t', 't2', 't6')\n",
    "FROM tmp\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1952fc8-58d2-4de3-b193-53f74164619c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mosaic",
   "language": "python",
   "name": "mosaic"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
